/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package da;

import entities.Customer;
import entities.User;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Bich Tran Van
 */
public class DACustomer {
    
    private DAUser daUser = new DAUser();
    private ArrayList<User> listUser ;
    MSSQLConnection conn = null;

    public DACustomer() {
        try {
            conn = new MSSQLConnection();
            conn.getConnection();
        } catch (Exception ex) {
            Logger.getLogger(DALoanType.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    public ArrayList<Customer> getAll(User user){
        ArrayList<Customer> list = new ArrayList<Customer>();
        try {
            conn.getConnection();
            if(user.getUserType().getTypeId() == 1){
                conn.cstm = conn.connect.prepareCall("{CALL View_all_Customer}");
            }else{
                conn.cstm = conn.connect.prepareCall("{CALL View_Customer_By_Branch(?)}");
                conn.cstm.setInt(1, user.getUserId());
            }
            conn.rs = conn.cstm.executeQuery();
            while (conn.rs.next()) {
                Customer customer = new Customer();
                
                customer.setCustomeId(conn.rs.getInt("CustomerID"));
                
                listUser = daUser.getById(conn.rs.getInt("UserID"));
                customer.setUser(listUser.get(0));
                customer.setFirstName(conn.rs.getString("FirstName"));
                customer.setLastName(conn.rs.getString("LastName"));
                customer.setAddress(conn.rs.getString("Address"));
                customer.setBirthDay(conn.rs.getDate("BrithDay"));
                customer.setEmail(conn.rs.getString("Email"));
                customer.setIdentity(conn.rs.getString("Identity"));
                customer.setGrossSalary(conn.rs.getDouble("GrossSalary"));
                customer.setOrganzination(conn.rs.getString("Organzination"));
                customer.setStatus(conn.rs.getBoolean("Status"));
                customer.setSex(conn.rs.getBoolean("Sex"));
                customer.setPhone(conn.rs.getString("PhoneNumber"));
                customer.setNote(conn.rs.getString("Note"));
                list.add(customer);
            }
            conn.myClose();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
    
    public ArrayList<Customer> getByID(int id,User user){
        ArrayList<Customer> list = new ArrayList<Customer>();
        try {
            conn.getConnection();
            if(user.getUserType().getTypeId() == 1){
                conn.cstm = conn.connect.prepareCall("{CALL View_Customer_By_ID(?)}");
            }else {
                conn.cstm = conn.connect.prepareCall("{CALL View_Customer_By_ID_Branch(?,?)}");
                conn.cstm.setInt(2, user.getUserId());
            }
            conn.cstm.setInt(1, id);
            conn.rs = conn.cstm.executeQuery();
            while (conn.rs.next()) {
                Customer customer = new Customer();
                
                customer.setCustomeId(conn.rs.getInt("CustomerID"));
                
                listUser = daUser.getById(conn.rs.getInt("UserID"));
                customer.setUser(listUser.get(0));
                
                customer.setFirstName(conn.rs.getString("FirstName"));
                customer.setLastName(conn.rs.getString("LastName"));
                customer.setAddress(conn.rs.getString("Address"));
                customer.setBirthDay(conn.rs.getDate("BrithDay"));
                customer.setEmail(conn.rs.getString("Email"));
                customer.setIdentity(conn.rs.getString("Identity"));
                customer.setGrossSalary(conn.rs.getDouble("GrossSalary"));
                customer.setOrganzination(conn.rs.getString("Organzination"));
                customer.setStatus(conn.rs.getBoolean("Status"));
                customer.setSex(conn.rs.getBoolean("Sex"));
                customer.setPhone(conn.rs.getString("PhoneNumber"));
                customer.setNote(conn.rs.getString("Note"));
                list.add(customer);
            }
            conn.myClose();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
    
    
    public ArrayList<Customer> getByID(int id){
        ArrayList<Customer> list = new ArrayList<Customer>();
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{CALL View_Customer_By_ID(?)}");
            conn.cstm.setInt(1, id);
            conn.rs = conn.cstm.executeQuery();
            while (conn.rs.next()) {
                Customer customer = new Customer();
                customer.setCustomeId(conn.rs.getInt("CustomerID"));
                
                listUser = daUser.getById(conn.rs.getInt("UserID"));
                customer.setUser(listUser.get(0));
                
                customer.setFirstName(conn.rs.getString("FirstName"));
                customer.setLastName(conn.rs.getString("LastName"));
                customer.setAddress(conn.rs.getString("Address"));
                customer.setBirthDay(conn.rs.getDate("BrithDay"));
                customer.setEmail(conn.rs.getString("Email"));
                customer.setIdentity(conn.rs.getString("Identity"));
                customer.setGrossSalary(conn.rs.getDouble("GrossSalary"));
                customer.setOrganzination(conn.rs.getString("Organzination"));
                customer.setStatus(conn.rs.getBoolean("Status"));
                customer.setSex(conn.rs.getBoolean("Sex"));
                customer.setPhone(conn.rs.getString("PhoneNumber"));
                customer.setNote(conn.rs.getString("Note"));
                list.add(customer);
            }
            conn.myClose();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
    
    public ArrayList<Customer> getByGross(double dGross,User user){
        ArrayList<Customer> list = new ArrayList<Customer>();
        try {
            conn.getConnection();
            if(user.getUserType().getTypeId() == 1){
                conn.cstm = conn.connect.prepareCall("{CALL View_Customer_By_Gross(?)}");
            }else {
                conn.cstm = conn.connect.prepareCall("{CALL View_Customer_By_Gross_Branch(?,?)}");
                conn.cstm.setInt(2, user.getUserId());
            }

            conn.cstm.setDouble(1, dGross);
            conn.rs = conn.cstm.executeQuery();
            while (conn.rs.next()) {
                Customer customer = new Customer();
                customer.setCustomeId(conn.rs.getInt("CustomerID"));
                
                listUser = daUser.getById(conn.rs.getInt("UserID"));
                customer.setUser(listUser.get(0));
                
                customer.setFirstName(conn.rs.getString("FirstName"));
                customer.setLastName(conn.rs.getString("LastName"));
                customer.setAddress(conn.rs.getString("Address"));
                customer.setBirthDay(conn.rs.getDate("BrithDay"));
                customer.setEmail(conn.rs.getString("Email"));
                customer.setIdentity(conn.rs.getString("Identity"));
                customer.setGrossSalary(conn.rs.getDouble("GrossSalary"));
                customer.setOrganzination(conn.rs.getString("Organzination"));
                customer.setStatus(conn.rs.getBoolean("Status"));
                customer.setSex(conn.rs.getBoolean("Sex"));
                customer.setPhone(conn.rs.getString("PhoneNumber"));
                customer.setNote(conn.rs.getString("Note"));
                list.add(customer);
            }
            conn.myClose();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
    
    public ArrayList<Customer> getByName(String name, User user) {
        ArrayList<Customer> list = new ArrayList<Customer>();
        try {
            conn.getConnection();
            if(user.getUserType().getTypeId() == 1){
                conn.cstm = conn.connect.prepareCall("{CALL View_Customer_By_Name(?)}");
                conn.cstm.setString(1, name);
            }else {
                conn.cstm = conn.cstm = conn.connect.prepareCall("{CALL View_Customer_By_Name_Branch(?,?)}");
                conn.cstm.setInt(1, user.getUserId());
                conn.cstm.setString(2, name);
            }
            
            
            conn.rs = conn.cstm.executeQuery();
            while (conn.rs.next()) {
                Customer customer = new Customer();
                customer.setCustomeId(conn.rs.getInt("CustomerID"));
                listUser = daUser.getById(conn.rs.getInt("UserID"));
                customer.setUser(listUser.get(0));
                customer.setFirstName(conn.rs.getString("FirstName"));
                customer.setLastName(conn.rs.getString("LastName"));
                customer.setAddress(conn.rs.getString("Address"));
                customer.setBirthDay(conn.rs.getDate("BrithDay"));
                customer.setEmail(conn.rs.getString("Email"));
                customer.setIdentity(conn.rs.getString("Identity"));
                customer.setGrossSalary(conn.rs.getDouble("GrossSalary"));
                customer.setOrganzination(conn.rs.getString("Organzination"));
                customer.setStatus(conn.rs.getBoolean("Status"));
                customer.setSex(conn.rs.getBoolean("Sex"));
                customer.setPhone(conn.rs.getString("PhoneNumber"));
                customer.setNote(conn.rs.getString("Note"));
                list.add(customer);
            }
            conn.myClose();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
    
    public ArrayList<Customer> getByOrganzination(String org, User user){
        ArrayList<Customer> list = new ArrayList<Customer>();
        try {
            conn.getConnection();
            if(user.getUserType().getTypeId() == 1){
                conn.cstm = conn.connect.prepareCall("{CALL View_Customer_By_Organzination(?)}");
            }else {
                conn.cstm = conn.connect.prepareCall("{CALL View_Customer_By_Organzination_Branch(?,?)}");
                conn.cstm.setInt(2, user.getUserId());
            }
            
            conn.cstm.setString(1, org);
            conn.rs = conn.cstm.executeQuery();
            while (conn.rs.next()) {
                Customer customer = new Customer();
                customer.setCustomeId(conn.rs.getInt("CustomerID"));
                listUser = daUser.getById(conn.rs.getInt("UserID"));
                customer.setUser(listUser.get(0));
                customer.setFirstName(conn.rs.getString("FirstName"));
                customer.setLastName(conn.rs.getString("LastName"));
                customer.setAddress(conn.rs.getString("Address"));
                customer.setBirthDay(conn.rs.getDate("BrithDay"));
                customer.setEmail(conn.rs.getString("Email"));
                customer.setIdentity(conn.rs.getString("Identity"));
                customer.setGrossSalary(conn.rs.getDouble("GrossSalary"));
                customer.setOrganzination(conn.rs.getString("Organzination"));
                customer.setStatus(conn.rs.getBoolean("Status"));
                customer.setSex(conn.rs.getBoolean("Sex"));
                customer.setPhone(conn.rs.getString("PhoneNumber"));
                customer.setNote(conn.rs.getString("Note"));
                list.add(customer);
            }
            conn.myClose();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
    
    public ArrayList<Customer> getByIdentity(String identity, User user){
        ArrayList<Customer> list = new ArrayList<Customer>();
        try {
            conn.getConnection();
            conn.getConnection();
            if(user.getUserType().getTypeId() == 1){
                conn.cstm = conn.connect.prepareCall("{CALL View_Customer_By_identity(?)}");
            }else {
                conn.cstm = conn.connect.prepareCall("{CALL View_Customer_By_identity_Branch(?,?)}");
                conn.cstm.setInt(2, user.getUserId());
            }
            
            conn.cstm.setString(1, identity);
            conn.rs = conn.cstm.executeQuery();
            while (conn.rs.next()) {
                Customer customer = new Customer();
                customer.setCustomeId(conn.rs.getInt("CustomerID"));
                listUser = daUser.getById(conn.rs.getInt("UserID"));
                customer.setUser(listUser.get(0));
                customer.setFirstName(conn.rs.getString("FirstName"));
                customer.setLastName(conn.rs.getString("LastName"));
                customer.setAddress(conn.rs.getString("Address"));
                customer.setBirthDay(conn.rs.getDate("BrithDay"));
                customer.setEmail(conn.rs.getString("Email"));
                customer.setIdentity(conn.rs.getString("Identity"));
                customer.setGrossSalary(conn.rs.getDouble("GrossSalary"));
                customer.setOrganzination(conn.rs.getString("Organzination"));
                customer.setStatus(conn.rs.getBoolean("Status"));
                customer.setSex(conn.rs.getBoolean("Sex"));
                customer.setPhone(conn.rs.getString("PhoneNumber"));
                customer.setNote(conn.rs.getString("Note"));
                list.add(customer);
            }
            conn.myClose();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
    
    public boolean deleteCustomer(int id) throws Exception{
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{call Disable_Customer(?)}");
            conn.cstm.setInt(1, id);
            int n = conn.cstm.executeUpdate();
            if(n > 0)
                return true;
            conn.myClose();
        return false;
    }

    public boolean enableCustomer(int id) throws Exception{
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{call Enable_Customer(?)}");
            conn.cstm.setInt(1, id);
            int n = conn.cstm.executeUpdate();
            if(n > 0)
                return true;
            conn.myClose();
        return false;
    }
    
    public boolean updateCustomer(Customer cus) throws Exception{
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{call Update_Customer(?,?,?,?,?,?,?,?,?,?,?,?,?)}");
            conn.cstm.setInt(1, cus.getCustomeId());
            conn.cstm.setInt(2, cus.getUser().getUserId());
            conn.cstm.setString(3, cus.getFirstName());
            conn.cstm.setString(4, cus.getLastName());
            conn.cstm.setString(5, cus.getAddress());
            conn.cstm.setDate(6,cus.getBirthDay());
            conn.cstm.setString(7, cus.getPhone());
            conn.cstm.setString(8, cus.getEmail());
            conn.cstm.setString(9, cus.getIdentity());
            conn.cstm.setBoolean(10, cus.isSex());
            conn.cstm.setString(11, cus.getOrganzination());
            conn.cstm.setDouble(12, cus.getGrossSalary());
            conn.cstm.setString(13, cus.getNote());
            int n= conn.cstm.executeUpdate();
            conn.myClose();
            if(n>0)
                return true;
        return false;
    }
    
    public boolean checkIdentity(String identity){
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{call sp_checkIdentityCus(?)}");
            conn.cstm.setString(1, identity);
            conn.rs = conn.cstm.executeQuery();
            while(conn.rs.next()){
                return true;
            }
            conn.myClose();
            
        } catch (Exception e) {
        }
        return false;
    }
    
    public boolean insertCustomer(Customer cus, int userID) throws Exception{
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{call Insert_Customer(?,?,?,?,?,?,?,?,?,?,?,?,?)}");
            conn.cstm.setInt(1, cus.getUser().getUserId());
            conn.cstm.setString(2, cus.getFirstName());
            conn.cstm.setString(3, cus.getLastName());
            conn.cstm.setString(4, cus.getAddress());
            conn.cstm.setDate(5,cus.getBirthDay());
            conn.cstm.setString(6, cus.getPhone());
            conn.cstm.setString(7, cus.getEmail());
            conn.cstm.setString(8, cus.getIdentity());
            conn.cstm.setBoolean(9, cus.isSex());
            conn.cstm.setString(10, cus.getOrganzination());
            conn.cstm.setDouble(11, cus.getGrossSalary());
            conn.cstm.setString(12, cus.getNote());
            conn.cstm.setBoolean(13, false);
            int n= conn.cstm.executeUpdate();
            conn.myClose();
            if(n>0)
                return true;
        return false;
    }
    
     public int getTotalCustomer() {
        int totalAmount = 0;
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{ CAlL cal_totalCustomer}");
            conn.rs = conn.cstm.executeQuery();
            while (conn.rs.next()) {
                totalAmount = conn.rs.getInt("TotalCustomer");
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        try {
            conn.myClose();
        } catch (SQLException ex) {
            Logger.getLogger(DALoanContact.class.getName()).log(Level.SEVERE, null, ex);
        }

        return totalAmount;
    }

     
    
}
